if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_leave_cancel')
          and type in ('P','PC'))
   drop procedure bd_proc_leave_cancel
go


create procedure bd_proc_leave_cancel (@orderId varchar(20),@operator varchar(20),@return_remark varchar(max)) as
BEGIN
	DECLARE @status int --单子的审批状态（3：审批通过，4：自动审批）
	DECLARE @BeginDate DATE --单子开始日期
	DECLARE @EndDate DATE --单子结束日期
	DECLARE @users VARCHAR(MAX) = '' --申请的人Id字符串（xxx,xxx,xxx,xxx）
---取消请假存储过程，已审批（通过）和未审批的请假单都能取消。
	begin try
		begin tran

			create table #resetUser(
				user_serial bigint null
			)
		
		
			--收集参数
			select @status = status,
							@BeginDate = start_date ,
							@EndDate = end_date
			from bd_leave_order 
			where order_id = @orderId;
		

			--1更新单子状态为-1 is_del（0:正常 ，-1：取消）并记录取消信息
			update bd_leave_order
				set is_del = -1,
					remove_user = @operator,
					return_remark = @return_remark,
					remove_time = getdate()
				where order_id = @orderId;
			
			insert into #resetUser 
			select user_serial from lr_kqingj  where order_id = @orderId and whether_validity = 1;
			
			--3记录重新计算
			--遍历调用
			DECLARE @user_serial bigint
			DECLARE @ishaveedu int
			
			DECLARE userOfReset CURSOR LOCAL FOR 
			select x.user_serial,x.ishaveedu from (
				select l.user_serial,ISNULL(k.ishaveedu, 0) as ishaveedu
				from lr_kqingj l 
				INNER JOIN kt_qingj k on k.bh = l.bh 
				where l.order_id = @orderId and l.whether_validity = 1
		) x GROUP BY x.user_serial,x.ishaveedu
		
			OPEN userOfReset FETCH NEXT FROM userOfReset INTO @user_serial,@ishaveedu  
			WHILE @@fetch_status = 0 
				BEGIN
						--重新计算
						exec bd_proc_reset_data @BeginDate,@ishaveedu,@user_serial,@operator
						
					FETCH NEXT FROM userOfReset INTO @user_serial,@ishaveedu
				END
			
			
			--4重新计算考勤
			if (@status=3 or @status=4)
				BEGIN
					SELECT
							@users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
						FROM (
								select DISTINCT user_serial from lr_kqingj where order_id = @orderId
						) u
					
					SET @users = SUBSTRING(@users,0, LEN(@users))    
					EXEC bd_proc_timecard_extend @BeginDate, @EndDate, @users,7
				END
		commit tran
	end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
				ROLLBACK TRANSACTION;
				RAISERROR (@errStr, 16, 1);
			END;
	end catch			
END
go
